package es.usj.dssie.videoclub.dao;

import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import es.usj.dssie.videoclub.dto.Client;

/**
 * Conexi�n a la BBDD para los alumnos.
 * 
 * La documentaci�n de esta clase y sus m�todos nos remitimos a la documentaci�n
 * especificada en el paquete {@link es.usj.tac.meteorologica.dao DAOPackage} donde explica
 * su uso y los m�todos que contiene, puesto que todas las clases son
 * pr�cticamente iguales.
 * @author Nacho 
 * @since 1.0
 * @version 1.0
 */
public class ClientJdbcDAO implements ClientDAO {
	
    public void create(Client obj) throws DAOException {
        Connection c = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            int i=1;
            c = ConnectionClassGenerator.getConnection();
            statement = c.prepareStatement("insert into Client (name, poblation) values (?, ?)");
            statement.setString(i++, obj.getName());
            statement.setInt(i++, obj.getPoblation());
            int n = statement.executeUpdate();
            if(n == 0)
                    throw new DAOException("No rows updated");
            result = statement.getGeneratedKeys();
            if(!result.next())
                    throw new DAOException("No generated keys");
            i=1;
            obj.setId(result.getInt(i++));
        } catch(SQLException sqle) {
            throw new DAOException(sqle);
        } catch (ClassNotFoundException e) {
        	  throw new DAOException(e);
		} finally {
            if(result != null)
                    try { result.close(); } catch(SQLException ignored) { }
            if(statement != null)
                    try { statement.close(); } catch(SQLException ignored) { }
            if(c != null)
                    try { c.close(); } catch(SQLException ignored) { }
        }
    }

   

    public Client read(int id) throws DAOException {
        Connection c = null;
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            int i=1;
            c = ConnectionClassGenerator.getConnection();
            statement = c.prepareStatement("select id, name, poblation from Client where id=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setInt(i++, id);
            result = statement.executeQuery();
            Client obj = null;
            if(result.next()) {
            	i=1;
                obj = new Client();
                obj.setId(result.getInt(i++));
                obj.setName(result.getString(i++));
                obj.setPoblation(result.getInt(i++));
                return obj;
            } else {
                throw new DAOException("No such instance exception");
            }
        } catch(SQLException sqle) {
            throw new DAOException(sqle);
        }catch (ClassNotFoundException e) {
      	  throw new DAOException(e);
		} finally {
            if(result != null)
                    try { result.close(); } catch(SQLException ignored) { }
            if(statement != null)
                    try { statement.close(); } catch(SQLException ignored) { }
            if(c != null)
                    try { c.close(); } catch(SQLException ignored) { }
        }
    }
    public Client readByName(String name) throws DAOException{
    	Connection c = null;
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            int i=1;
            c = ConnectionClassGenerator.getConnection();
            statement = c.prepareStatement("select id, name, poblation from Client where name=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setString(i++, name);
            result = statement.executeQuery();
            Client obj = null;
            if(result.next()) {
            	i=1;
                obj = new Client();
                obj.setId(result.getInt(i++));
                obj.setName(result.getString(i++));
                obj.setPoblation(result.getInt(i++));
                return obj;
            } else {
               return null;
            }
        } catch(SQLException sqle) {
            throw new DAOException(sqle);
        }catch (ClassNotFoundException e) {
      	  throw new DAOException(e);
		} finally {
            if(result != null)
                    try { result.close(); } catch(SQLException ignored) { }
            if(statement != null)
                    try { statement.close(); } catch(SQLException ignored) { }
            if(c != null)
                    try { c.close(); } catch(SQLException ignored) { }
        }
    }
    public List<Client> listByCountry(int id) throws DAOException {
        Connection c = null;
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            int i=1;
            c = ConnectionClassGenerator.getConnection();
            statement = c.prepareStatement("select id, name, poblation from Client where poblation=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setInt(i++, id);
            result = statement.executeQuery();
            List<Client> list = new ArrayList<Client>();
            while(result.next()) {
            	i=1;
            	Client obj = new Client();
                obj.setId(result.getInt(i++));
                obj.setName(result.getString(i++));
                obj.setPoblation(result.getInt(i++));
                list.add(obj);
            } 
            
            return list;
        } catch(SQLException sqle) {
            throw new DAOException(sqle);
        }catch (ClassNotFoundException e) {
      	  throw new DAOException(e);
		} finally {
            if(result != null)
                    try { result.close(); } catch(SQLException ignored) { }
            if(statement != null)
                    try { statement.close(); } catch(SQLException ignored) { }
            if(c != null)
                    try { c.close(); } catch(SQLException ignored) { }
        }
    }
    

}
